﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace BusinessLogicLayer
{
    public class RequisitionFacade
    {
        InventorySoreDataContext context;
        Requisition requisition;
        public RequisitionFacade()
        {
            context = new InventorySoreDataContext();
        }

        public IQueryable FindAllRequisitions()
        {
            var requisitions = from req in context.Requisitions select req;
            return requisitions;
        }

        public RequisitionDetail FindRequisitionDetailsByIDAndItemCode(string reqID, string itmID)
        {
            var requisitionDetail = (from reqDetail in context.RequisitionDetails where reqDetail.FormID == reqID && reqDetail.ItemCode == itmID select reqDetail).Single();
            return requisitionDetail;
        }

        public Requisition FindRequisitionFormById(string formID)
        {
            var requisitions = (from req in context.Requisitions where req.FormID == formID select req).Single();
            return requisitions;
        }

        public IQueryable FindPendingRejectedForm(string empid)
        {
            var forms = (from form in context.Requisitions
                         where form.EmployeeID == empid
                         && form.Status != "Approved"
                         select new
                         {
                             formID = form.FormID,
                             date = form.RequisitionDate,
                             status = form.Status,
                         });
            return forms;
        }

        public IQueryable FindHistory(string employeeID, DateTime fromDate, DateTime toDate, string status)
        {
            try
            {
                var histories = from history in context.Requisitions
                                where history.EmployeeID == employeeID && history.RequisitionDate >= fromDate && history.RequisitionDate <= toDate && history.Status == status
                                select new
                                {
                                    formID = history.FormID,
                                    approvedDate = history.ApprovedDate,
                                };
                return histories;
            }
            catch (Exception ee)
            {
                return null;
            }

        }

        public IQueryable GetRequisitionDetails(string empid)
        {
            string deptCode = new DepartmentFacade().getDepartmentCode(empid);
            var select = (from req in context.Requisitions
                          where req.Status == "Pending" && req.Employee.Departmentcode == deptCode
                          select new
                          {
                              RequisitionID = req.FormID,
                              Date = req.RequisitionDate,
                              EmployeeName = req.Employee.EmployeeName,
                              Remark = req.Remarks,
                          });
            return select;
        }

        public IQueryable getRequsitionDetails(string formID)
        {
            try
            {
                var query = from requisitionDetails in context.RequisitionDetails
                            where requisitionDetails.FormID == formID
                            select new GetRetrivalDetails
                            {
                                ItemCode = requisitionDetails.ItemCode,
                                ItemName = requisitionDetails.Item.Description,
                                CategoryName = requisitionDetails.Item.Category.CategoryName,
                                Qty = Convert.ToString(requisitionDetails.Qty),
                            };
                return query;
            }
            catch (Exception ee)
            {
                return null;
            }
        }
        //public IQueryable ViewReportByCategoryInMonth(int sMonth)
        //{
        //    var items = (from r in context.RequisitionDetails
        //                 join i in context.Items on r.ItemCode equals i.ItemCode
        //                 join c in context.Categories on i.CategoryID equals c.CategoryID
        //                 join re in context.Requisitions on r.FormID equals re.FormID
        //                 where re.RequisitionDate.value.Month == sMonth
        //                 group r by new { r.ItemCode, i.Description, c.CategoryName, i.UOM }
        //                     into gp
        //                     select new
        //                     {
        //                         ItemCode = gp.Key.ItemCode,
        //                         Description = gp.Key.Description,
        //                         Categories = gp.Key.CategoryName,
        //                         Qty = gp.Sum(r => r.Qty),
        //                         UOM = gp.Key.UOM
        //                     });
        //    return items;
        //}
        //public IQueryable ViewReportByEmployeeInMonth(int sMonth)
        //{

        //public static void Main()
        //{
        //    RequisitionFacade reqf = new RequisitionFacade();
        //    foreach (var v in reqf.GetRequisitionDetails("A0102934K"))
        //    {

        //    }
        //    Console.Read();
        //}

        public void UpdateStatus(string formID)
        {
            requisition = FindRequisitionFormById(formID);
            requisition.Status = "Pending";
            context.SubmitChanges();
        }

        public IQueryable ViewReportByCategoryInMonth(int sMonth)
        {
            var items = (from i in context.Items
                         join rd in context.RequisitionDetails on i.ItemCode equals rd.ItemCode
                         join r in context.Requisitions on rd.FormID equals r.FormID
                         join e in context.Employees on r.EmployeeID equals e.EmployeeID
                         where r.RequisitionDate.Value.Month == sMonth
                         group rd by new { i.ItemCode, i.Description, e.EmployeeName, i.UOM }
                             into gp
                             select new
                             {
                                 ItemCode = gp.Key.ItemCode,
                                 Description = gp.Key.Description,
                                 Employee = gp.Key.EmployeeName,
                                 Qty = gp.Sum(rd => rd.Qty),
                                 UOM = gp.Key.UOM
                             });
            return items;
        }
        public IQueryable ViewReportByEmployeeInYear(int sYear)
        {
            var items = (from i in context.Items
                         join rd in context.RequisitionDetails on i.ItemCode equals rd.ItemCode
                         join r in context.Requisitions on rd.FormID equals r.FormID
                         join e in context.Employees on r.EmployeeID equals e.EmployeeID
                         where r.RequisitionDate.Value.Year == sYear
                         group rd by new { i.ItemCode, i.Description, e.EmployeeName, i.UOM }
                             into gp
                             select new
                             {
                                 ItemCode = gp.Key.ItemCode,
                                 Description = gp.Key.Description,
                                 Employee = gp.Key.EmployeeName,
                                 Qty = gp.Sum(rd => rd.Qty),
                                 UOM = gp.Key.UOM
                             });
            return items;
        }
        public IQueryable ViewReportByEmployeeInMonth(int sMonth)
        {
            var items = (from i in context.Items
                         join rd in context.RequisitionDetails on i.ItemCode equals rd.ItemCode
                         join r in context.Requisitions on rd.FormID equals r.FormID
                         join e in context.Employees on r.EmployeeID equals e.EmployeeID
                         where r.RequisitionDate.Value.Month == sMonth
                         group rd by new { i.ItemCode, i.Description, e.EmployeeName, i.UOM }
                             into gp
                             select new
                             {
                                 ItemCode = gp.Key.ItemCode,
                                 Description = gp.Key.Description,
                                 Employee = gp.Key.EmployeeName,
                                 Qty = gp.Sum(rd => rd.Qty),
                                 UOM = gp.Key.UOM
                             });

            return items;
        }
        
        public IQueryable ViewReportByCategoryInYear(int sYear)
        {
            var items = (from r in context.RequisitionDetails
                         join i in context.Items on r.ItemCode equals i.ItemCode
                         join c in context.Categories on i.CategoryID equals c.CategoryID
                         join re in context.Requisitions on r.FormID equals re.FormID
                         where re.RequisitionDate.Value.Year == sYear
                         group r by new { r.ItemCode, i.Description, c.CategoryName, i.UOM }
                             into gp
                             select new
                             {
                                 ItemCode = gp.Key.ItemCode,
                                 Description = gp.Key.Description,
                                 Categories = gp.Key.CategoryName,
                                 Qty = gp.Sum(r => r.Qty),
                                 UOM = gp.Key.UOM
                             });
            return items;
        }



        public void addRequisitionDetails(List<RequisitionDetail> details, string formID)
        {
            foreach (RequisitionDetail req in details)
            {
                var reqDetail = FindItemsByReqFormID(formID);
                RequisitionDetail item = FindItemsByReqFormIDandItemCode(formID, req.ItemCode);
                if (item != null)
                {
                    item.Qty = req.Qty;
                    context.SubmitChanges();
                }
                else
                {
                    context.RequisitionDetails.InsertOnSubmit(req);
                }
                context.SubmitChanges();
            }
        }

        public RequisitionDetail FindItemsByReqFormIDandItemCode(string formID, string itemCode)
        {
            var items = (from itm in context.RequisitionDetails
                         where itm.FormID == formID && itm.ItemCode == itemCode
                         select itm).SingleOrDefault();
            return items;
        }

        public void insertRequisition(Requisition requisition, string employeeID, RequisitionDetail[] requ)
        {
            try
            {
                requisition.LastModified = DateTime.Now;
                requisition.ApprovedDate = DateTime.Now;
                context.Requisitions.InsertOnSubmit(requisition);
                context.RequisitionDetails.InsertAllOnSubmit(requ);
                context.SubmitChanges();
            }

            catch (Exception ee)
            {
                throw new Exception("Data is already existed!");
            }

        }

        public string GetFormID(string employeeID)
        {
            try
            {
                string newFormID;
                string departmentCode = new DepartmentFacade().getDepartmentCode(employeeID);
                var query = (from requ in context.Requisitions
                             where requ.Employee.Departmentcode == departmentCode
                             select requ);
                if (query.Count() != 0)
                {
                    int code = query.Count();
                    code++;
                    code = code + 100;

                    newFormID = departmentCode + "/" + code + "/" + Convert.ToString(DateTime.Now.Year);
                }
                else
                {
                    newFormID = departmentCode + "/100/" + Convert.ToString(DateTime.Now.Year);
                }
                return newFormID;
            }
            catch (Exception ee)
            {
                return null;
            }
        }

        public IQueryable FindByApprove(string employeeID)
        {
            try
            {
                string departmentCode = new DepartmentFacade().getDepartmentCode(employeeID);
                var requisitions = from requ in context.Requisitions
                                   where requ.Employee.EmployeeID == employeeID && requ.Status == "Approved"
                                   select new
                                   {
                                       FormID = requ.FormID,
                                       Date = requ.RequisitionDate,
                                       EmployeeName = requ.Employee.EmployeeName,
                                   };
                return requisitions;
            }
            catch (Exception ee)
            {
                return null;
            }
        }

        public IQueryable FindItemsByReqFormID(string formID)
        {
            var items = from itm in context.RequisitionDetails
                        where itm.FormID == formID
                        select itm;
            return items;
        }

        public IQueryable GetRequisition()
        {
            var requisitionForm = (from req in context.Requisitions
                                   where req.Status == "Pending" && req.FormID.Contains(req.Employee.Departmentcode)
                                   select new
                                   {
                                       RequisitionFormID = req.FormID,
                                       Date = req.RequisitionDate,
                                       EmployeeName = req.Employee.EmployeeName,
                                       Remark = req.Remarks,
                                   });
            return requisitionForm;
        }

        public void RejectRequisitionForm(string formId, string remarks)
        {
            requisition = FindRequisitionFormById(formId);
            requisition.Status = "Rejected";
            requisition.RemarksByHead = remarks;
            context.SubmitChanges();
        }

        public void ApprovedRequisitionStatusById(string formId)
        {
            requisition = FindRequisitionFormById(formId);
            requisition.Status = "Approved";
            try
            {
                context.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.StackTrace);
            }
        }

        public void SendApprovedEmail(string lblempID, string reqId, string status)
        {
            Employee emp = new EmployeeFacade().FindEmployeeByID(lblempID);
            BusinessLogicLayer.Department dept = new DepartmentFacade().FindDepartmentByDepartmentID(emp.Departmentcode);
            Requisition req = new RequisitionFacade().FindRequisitionFormById(reqId);
            //need to change......
            string emailTo = "aungmyatthu.sai@gmail.com";
            string emailFrom = "nirmal.gtm@gmail.com";
            string subject = "Notification for Requisition Form";
            string body = ApprovedMailBody(emp, dept, req, status);
            new SendEmail().Send(emailFrom, emailTo, subject, body);
        }

        public void SendUnderReorderEmail(string lblempID, string reqId, string status)
        {
            EmployeeFacade empFac = new EmployeeFacade();
            Employee emp = empFac.FindEmployeeByID(lblempID);
            BusinessLogicLayer.Department dept = new DepartmentFacade().FindDepartmentByDepartmentID(emp.Departmentcode);
            string clerkEmail = empFac.FindEmployeeByID(dept.ClerkID).Email;
            Requisition req = new RequisitionFacade().FindRequisitionFormById(reqId);
            //need to change......
            string emailTo = "aungmyatthu.sai@gmail.com";
            string emailFrom = "nirmal.gtm@gmail.com";
            string subject = "Notification for Requisition Form";
            string body = UnderReorderMailBody(emp, dept, req, status);
            new SendEmail().Send(emailFrom, emailTo, subject, body);
        }

        public string ApprovedMailBody(Employee emp, BusinessLogicLayer.Department dept, Requisition req, string status)
        {
            string body = "<DT>Dear " + emp.EmployeeName + ",";
            body += "<p>" + "<DD>" + "This email is to notify you that the requisition form No.<b>" + req.FormID + "</b> from your department for this week is '<b>" + status + "<b>'.</DD>";
            body += "<br><br><br>";
            body += "Regards,<br>" + emp.EmployeeName;
            return body;
        }

        public string UnderReorderMailBody(Employee emp, BusinessLogicLayer.Department dept, Requisition req, string status)
        {
            string body = "<DT>Dear " + emp.EmployeeName + ",";
            body += "<p>" + "<DD>" + "The particular quantity of item that you requested is under the reorder quantity.<br/>"+
                "Please reconsider about the Order.<b>"+ req.FormID + "</b> from your department for this week is '<b>" + status + "<b>'.</DD>";
            body += "<br><br><br>";
            body += "Regards,<br>" + emp.EmployeeName;
            return body;
        }
    }
}
